package me.zynaga.ragecraft;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.logging.Level;

public final class Database {

    private Ragecraft plugin;
    public String dbName = "data.db";
    public boolean isReady = false;
    public File dbFile = null;
    public Connection conn = null;

    public Database(Ragecraft instance) {

        plugin = instance;

        dbFile = new File(plugin.pluginPath, dbName);

        int updateStart = 0;

        String dbUpdate[][] = {
            {
                "CREATE TABLE [rc_auctions] ([id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,[user] TEXT NULL,[item] INTEGER  NULL,[idata] INTEGER  NULL,[quantity] INTEGER  NULL,[expires] INTEGER  NULL,[bid] FLOAT  NULL,[buyout] FLOAT  NULL)",
                "CREATE TABLE [rc_auctions_bids] ([id_auction] INTEGER  NULL,[user] TEXT NULL,[bid] FLOAT  NULL)",
                "CREATE TABLE [rc_dialogs] ([id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,[message] TEXT  NULL)",
                "CREATE TABLE [rc_items] ([name] TEXT  NULL,[item] INTEGER  NULL,[idata] INTEGER  NOT NULL,[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT)",
                "CREATE TABLE [rc_items_data] ([item] INTEGER  NULL,[idata] INTEGER  NULL,[id_world] INTEGER  NULL,[price_sell] FLOAT  NULL,[price_buy] FLOAT  NULL,[auction_fee] FLOAT  NULL)",
                "CREATE TABLE [rc_kits] ([id_group] INTEGER  NULL,[user] TEXT  NULL,[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,[name] TEXT  NULL)",
                "CREATE TABLE [rc_kits_items] ([id_kit] INTEGER  NOT NULL,[item] INTEGER  NULL,[idata] INTEGER  NULL,[quantity] INTEGER  NULL)",
                "CREATE TABLE [rc_mails] ([id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,[user_from] TEXT  NULL,[user_to] TEXT  NULL,[subject] TEXT  NULL,[message] TEXT  NULL,[coins] FLOAT  NULL,[type] INTEGER  NULL)",
                "CREATE TABLE [rc_mails_attachs] ([id_mail] INTEGER  NULL,[item] INTEGER  NULL,[idata] INTEGER  NULL,[quantity] INTEGER  NULL)",
                "CREATE TABLE [rc_mobs] ([name] TEXT  NULL,[drop_coin_chance] FLOAT  NULL,[drop_coin_max] FLOAT  NULL,[drop_coin_min] FLOAT  NULL,[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT)",
                "CREATE TABLE [rc_mobs_drops] ([chance] FLOAT  NULL,[quantity] INTEGER  NULL,[idata] INTEGER  NULL,[item] INTEGER  NULL,[id_mob] INTEGER  NULL)",
                "CREATE TABLE [rc_npcs] ([skin] TEXT  NULL,[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,[name] TEXT  NULL,[type] TEXT  NULL)",
                "CREATE TABLE [rc_quests] ([id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,[name] TEXT  NULL,[dialog_start] TEXT  NULL,[xp] INTEGER  NULL,[coins] FLOAT  NULL,[dialog_end] TEXT  NULL)",
                "CREATE TABLE [rc_quests_rewards] ([id_quest] INTEGER  NULL,[item] INTEGER  NULL,[idata] INTEGER  NULL,[quanitity] INTEGER  NULL,[type] TEXT  NULL)",
                "CREATE TABLE [rc_quests_rules] ([id_quest] INTEGER  NULL,[type] TEXT  NULL,[action] TEXT  NULL,[quantity] INTEGER  NULL)",
                "CREATE TABLE [rc_regions] ([name] TEXT  NULL,[id_world] INTEGER  NULL,[id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,[z2] FLOAT  NULL,[y2] FLOAT  NULL,[x2] FLOAT  NULL,[z1] FLOAT  NULL,[y1] FLOAT  NULL,[x1] FLOAT  NULL)",
                "CREATE TABLE [rc_regions_rules] ([id_region] INTEGER  NULL,[rule] TEXT  NULL,[value] TEXT  NULL)",
                "CREATE TABLE [rc_users_data] ([user] TEXT NOT NULL,[data] TEXT  NULL,[value] TEXT  NULL)",
                "CREATE TABLE [rc_worlds] ([id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,[name] TEXT  NULL)",
                "CREATE TABLE [rc_worlds_rules] ([id_world] INTEGER  NULL,[value] TEXT  NULL,[rule] TEXT  NULL)",
                "CREATE UNIQUE INDEX [IDX_RC_ITEMS_DATA_] ON [rc_items_data]([id_world]  ASC,[item]  ASC,[idata]  ASC)",
                "CREATE UNIQUE INDEX [IDX_RC_KITS_ITEMS_] ON [rc_kits_items]([item]  ASC,[id_kit]  ASC,[idata]  ASC)",
                "CREATE UNIQUE INDEX [IDX_RC_MAILS_ATTACHS_] ON [rc_mails_attachs]([item]  ASC,[idata]  ASC,[id_mail]  ASC)",
                "CREATE UNIQUE INDEX [IDX_RC_MOBS_DROPS_] ON [rc_mobs_drops]([id_mob]  ASC,[item]  ASC,[idata]  ASC)",
                "CREATE UNIQUE INDEX [IDX_RC_QUESTS_RULES_] ON [rc_quests_rules]([id_quest]  ASC,[type]  ASC,[action]  ASC)",
                "CREATE UNIQUE INDEX [IDX_RC_REGIONS_RULES_] ON [rc_regions_rules]([id_region]  ASC,[rule]  ASC)",
                "CREATE UNIQUE INDEX [IDX_RC_USERS_DATA_] ON [rc_users_data]([user]  ASC,[data]  ASC)",
                "CREATE UNIQUE INDEX [IDX_RC_WORLDS_] ON [rc_worlds]([name]  ASC)",
                "CREATE UNIQUE INDEX [IDX_RC_WORLDS_RULES_] ON [rc_worlds_rules]([id_world]  ASC,[rule]  ASC)",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,0,'lava-spread')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,0,'fire-spread')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'fire-set')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'water-spread')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,0,'tnt')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,2,'creeper-damage')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,256,'navigate-item')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,257,'selector-item')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'rpg-enabled')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'buy')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'rpg-sell')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'rpg-auctions')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'rpg-quests')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'rpg-repair')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,'Coin(s)','currency')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'rpg-currency')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'permissions')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'rpg-mails')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'regions-enabled')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,30,'rpg-mails-cost')",
                "INSERT OR IGNORE INTO rc_worlds_rules VALUES(0,1,'rc-version')"
            }
        };

        try {

            if (dbFile.exists() == false) {

                dbFile.createNewFile();

            }

            connect();

            ResultSet data = query("SELECT value FROM rc_worlds_rules WHERE id_world = 0 AND rule = 'rc-version'", false);

            if (data != null) {

                updateStart = Integer.parseInt(data.getString("value"));

            }

            ArrayList<ArrayList<String>> dbUpdateStatic = new ArrayList<ArrayList<String>>();

            for (int i = updateStart; i < dbUpdate.length; i++) {

                dbUpdateStatic.add(new ArrayList());
                dbUpdateStatic.get(i).addAll(Arrays.asList(dbUpdate[i]));

            }

            if (updateStart == 0 || data == null) {

                conn.close();

                if (dbFile.delete() == true) {

                    dbFile.createNewFile();

                }

                for (int i = 0; i < plugin.getServer().getWorlds().size(); i++) {

                    dbUpdateStatic.get(0).add("INSERT OR IGNORE INTO rc_worlds VALUES(NULL,'" + plugin.getServer().getWorlds().get(i).getName() + "')");

                }
                /*
                Set<OfflinePlayer> ops = plugin.getServer().getOperators();
                
                for (int i = 0; i < ops.size(); i++) {
                
                ops.toArray()[0]
                plugin.
                
                }
                /**/
                connect();

            }

            if (updateStart < dbUpdate.length) {

                plugin.log("Creating or updating database...");

            }

            for (int i = updateStart; i < dbUpdateStatic.size(); i++) {

                for (int j = 0; j < dbUpdateStatic.get(i).size(); j++) {

                    query(dbUpdateStatic.get(i).get(j).toString());

                }

            }

            isReady = true;

        } catch (Exception e) {

            plugin.log("Couldn't create or update database." + e.getMessage(), Level.SEVERE);

        }

    }

    private void connect() {

        try {

            Class.forName("org.sqlite.JDBC");

            conn = DriverManager.getConnection("jdbc:sqlite:" + plugin.pluginPath + dbName);

        } catch (Exception e) {

            plugin.log(e.getMessage(), Level.SEVERE);

        }


    }

    public ResultSet query(String query) {

        return query(query, true);

    }

    public ResultSet query(String query, boolean forceLog) {

        ResultSet sqlData = null;

        try {

            sqlData = conn.createStatement().executeQuery(query + ";");

        } catch (SQLException e) {

            if (e.getMessage().equalsIgnoreCase("query does not return ResultSet") == false && forceLog) {
                plugin.log("[" + e.getErrorCode() + "] " + e.getMessage() + "\r\nQuery: " + query, Level.WARNING);
            }

        }

        return sqlData;

    }
}
